package com.hospital.Common;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;
import com.hospital.Entity.MsgItem;
import com.hospital.Entity.RootRegion;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * 数据库Common类
 * Created by wangjun on 2015/5/25.
 */
public class CommonDao {

    private static AppSQLiteHelper dbHelper;
    private static SQLiteDatabase database;
    private static CommonDao commonDao;

    public static CommonDao getInstance(Context context){
        if (commonDao == null) {
            synchronized (CommonDao.class) {
                if (commonDao == null) {
                    commonDao = new CommonDao();
                    dbHelper = new AppSQLiteHelper(context,AppSQLiteHelper.DB_NAME,null,AppSQLiteHelper.VERSION);
                    database = dbHelper.getWritableDatabase();
                }
            }
        }
        return commonDao;
    }

    public void addLastRegion(RootRegion region){
        ContentValues contentValues = new ContentValues();
        contentValues.put("region_id",region.getRegionId());
        contentValues.put("region_name", region.getName());
        contentValues.put("created_time",new Date().getTime());
        database.replace(AppSQLiteHelper.LAST_REGION_TABLE, null, contentValues);
    }

    public List<RootRegion> getLastRegions(){
        List<RootRegion> regions = new ArrayList<RootRegion>();
        Cursor cursor = database.query(AppSQLiteHelper.LAST_REGION_TABLE,new String[]{"region_id","region_name"},null,null,null,null,"created_time DESC");
        if(cursor.moveToFirst()){
            do {
                int regionId = cursor.getInt(0);
                String regionName = cursor.getString(1);
                RootRegion region = new RootRegion();
                region.setRegionId(regionId);
                region.setName(regionName);
                regions.add(region);
            }while (cursor.moveToNext());
        }
        cursor.close();
        return regions;
    }



    public int getLastMessageId(int userId){
        Cursor cursor = database.rawQuery("select max(MessageId) from message where TargetId = ?", new String[]{String.valueOf(userId)});
        if (cursor.moveToFirst())
            return  cursor.getInt(0);
        return 0;
    }

    public  int getContainMessage( int msgId){
        Cursor cursor = database.rawQuery("select count(*) from message where MessageId = ?", new String[]{String.valueOf(msgId)});
        if (cursor.moveToFirst())
            return  cursor.getInt(0);
        return 0;
    }

    /**
     * 标记消息已读
     * @param msgId
     * @return 是否标记成功
     */
    public boolean ReadMessage(int msgId){
        ContentValues cv = new ContentValues();
        cv.put("Read", true);
        MsgItem mi = getMessage(msgId);
        return database.update(AppSQLiteHelper.MESSAGE_TABLE, cv, "MessageId = ? OR (MESSAGETYPEID NOT IN (1,2) AND JobTypeId = ? AND JobId = ?)",
                new String[]{
                        String.valueOf(mi.getMessageId()),
                        String.valueOf(mi.getJobTypeId()),
                        String.valueOf(mi.getJobId())
                }) == 1;
    }

    /**
     *删除消息
     * @param msgId
     */
    public void DelMessage(int msgId){
        database.delete(AppSQLiteHelper.MESSAGE_TABLE,"MessageId=?",new String[]{String.valueOf(msgId)});
    }

    /**
     * 获取未读消息数量
     * @return
     */
    public int getUnreadMessageCount(int userId){
        Cursor cur = database.rawQuery("SELECT COUNT(*) FROM MESSAGE WHERE Read = 0 AND TargetId = ?", new String[]{String.valueOf(userId)});
        if (cur.moveToFirst())
            return cur.getInt(0);
        return 0;
    }

    public List<MsgItem> getMessageList(int userId){
        try {

            Cursor cur = database.rawQuery("" +
                            "SELECT * FROM MESSAGE WHERE TARGETID = ? AND MESSAGETYPEID IN (1,2)\n" +
                            "UNION\n" +
                            "SELECT * FROM MESSAGE WHERE TARGETID = ? AND MESSAGETYPEID NOT IN (1,2) GROUP BY JobTypeId,JobId HAVING MessageId = MAX(MessageId) AND JobTypeId > 0 AND JobId > 0\n" +
                            "ORDER BY MESSAGEID DESC",
                    new String[]{
                            String.valueOf(userId),
                            String.valueOf(userId)
                    });
            //todo: 相同岗位相同岗位类型的只出现一条

            if (cur.moveToFirst() == false) return null;

            List<MsgItem> msgList = new ArrayList<MsgItem>();

            do {
                MsgItem mi = new MsgItem();
                mi.setMessageId(cur.getInt(cur.getColumnIndex("MessageId")));
                mi.setSendingId(cur.getInt(cur.getColumnIndex("SendingId")));
                mi.setVoiceAlert(cur.getInt(cur.getColumnIndex("VoiceAlert")) > 0);
                mi.setJobTypeId(cur.getInt(cur.getColumnIndex("JobTypeId")));
                mi.setJobId(cur.getInt(cur.getColumnIndex("JobId")));
                mi.setDateCreated(new Date(cur.getLong(cur.getColumnIndex("DateCreated"))));
                mi.setMessageTitle(cur.getString(cur.getColumnIndex("MessageTitle")));
                mi.setMessage(cur.getString(cur.getColumnIndex("Message")));
                mi.setMessageTypeId(cur.getInt(cur.getColumnIndex("MessageTypeId")));
                mi.setMessageType(cur.getString(cur.getColumnIndex("MessageType")));
                mi.setRead(cur.getInt(cur.getColumnIndex("Read")) > 0);
                mi.setVibrate(cur.getInt(cur.getColumnIndex("Vibrate")) > 0);
                mi.setTargetId(cur.getInt(cur.getColumnIndex("TargetId")));
                msgList.add(mi);
            } while (cur.moveToNext());

            return msgList;
        }catch (Exception ex){
            ex.printStackTrace();
        }
        return null;
    }

    /**
     * 获取消息内容
     * @param messageId
     * @return
     */
    public MsgItem getMessage(int messageId){
        try {

            Cursor cur = database.rawQuery("SELECT * FROM MESSAGE WHERE MessageId = ?",
                    new String[]{
                            String.valueOf(messageId),
                    });

            if (cur.moveToFirst() == false) return null;

            MsgItem mi = new MsgItem();
            mi.setMessageId(cur.getInt(cur.getColumnIndex("MessageId")));
            mi.setSendingId(cur.getInt(cur.getColumnIndex("SendingId")));
            mi.setVoiceAlert(cur.getInt(cur.getColumnIndex("VoiceAlert")) > 0);
            mi.setJobTypeId(cur.getInt(cur.getColumnIndex("JobTypeId")));
            mi.setJobId(cur.getInt(cur.getColumnIndex("JobId")));
            mi.setDateCreated(new Date(cur.getLong(cur.getColumnIndex("DateCreated"))));
            mi.setMessageTitle(cur.getString(cur.getColumnIndex("MessageTitle")));
            mi.setMessage(cur.getString(cur.getColumnIndex("Message")));
            mi.setMessageTypeId(cur.getInt(cur.getColumnIndex("MessageTypeId")));
            mi.setMessageType(cur.getString(cur.getColumnIndex("MessageType")));
            mi.setRead(cur.getInt(cur.getColumnIndex("Read")) > 0);
            mi.setVibrate(cur.getInt(cur.getColumnIndex("Vibrate")) > 0);
            mi.setTargetId(cur.getInt(cur.getColumnIndex("TargetId")));

            return mi;
        }catch (Exception ex){
            ex.printStackTrace();
        }
        return null;
    }



    public void close(){
        database.close();
    }

}
